package com.gxuwz.leaveSystem.dao;

import com.gxuwz.leaveSystem.entity.Student;

import com.gxuwz.leaveSystem.entity.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;


import java.util.List;

public class StudentDao {
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 添加学生
     * @param student
     * @return
     */
    public int addStudent(Student student) {
        String sql = "INSERT INTO sys_student (stuID, classID, stuName, sex, address, stuTel, contact, contactTel) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        Object[] object =new Object[]{
                student.getStuID(),
                student.getClassID(),
                student.getStuName(),
                student.getSex(),
                student.getAddress(),
                student.getStuTel(),
                student.getContact(),
                student.getContactTel()
        };
        int num = this.jdbcTemplate.update(sql,object);
        return num;
    }
    /**
     * 删除学生
     * @param id
     * @return
     */
    public int deleteStudent(String id) {
        String sql = "DELETE FROM sys_student WHERE stuID=?";
        int num = this.jdbcTemplate.update(sql,id);
        return num;
    }

    /**
     * 更新学生
     * @param student
     * @return
     */
    public int updateStudent(Student student) {
        String sql = "UPDATE sys_student SET classID=?, stuName=?, sex=?, address=?, stuTel=?, contact=?, contactTel=? WHERE stuID=?";
        Object[] object =new Object[]{
                student.getClassID(),
                student.getStuName(),
                student.getSex(),
                student.getAddress(),
                student.getStuTel(),
                student.getContact(),
                student.getContactTel(),
                student.getStuID()
        };
        int num = this.jdbcTemplate.update(sql,object);
        return num;
    }


    /**
     * 单个查询
     * @param id
     * @return
     */
    public Student getStudent(String id) {
        String sql = "SELECT * FROM sys_student WHERE stuID=?";
        RowMapper<Student> rowMapper = new BeanPropertyRowMapper<Student>(Student.class);
        //通过rowmapper返回单行记录
        return  this.jdbcTemplate.queryForObject(sql,rowMapper,id);
    }

    /**
     * 查询全部学生
     * @return
     */
    public List<Student> getAllStudents() {
        String sql = "SELECT * FROM sys_student";
        RowMapper<Student> rowMapper = new BeanPropertyRowMapper<Student>(Student.class);
        return  this.jdbcTemplate.query(sql,rowMapper);
    }

    /**
     * 根据关键字搜索学生
     * @param keyword
     * @return 列表
     */
    public List<Student> searchStudent(String keyword) {
        String sql = "SELECT * FROM sys_student WHERE stuID LIKE ? OR classID LIKE ? OR stuName LIKE ? OR sex LIKE ? OR address LIKE ? OR stuTel LIKE ? OR contact LIKE ?OR contactTel LIKE ?";
        return jdbcTemplate.query(sql, new Object[]{"%" + keyword + "%","%" + keyword + "%","%" + keyword + "%","%" + keyword + "%","%" + keyword + "%","%" + keyword + "%", "%" + keyword + "%", "%" + keyword + "%"}, new BeanPropertyRowMapper<>(Student.class));
    }


}